﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NTQOnlineExam.Services.Infrastructure;

namespace NTQOnlineExam.Services.CommandService.TermSection
{
    public class ChangeSectionOrderDbCommand : ICommand<bool>
    {
        private readonly int _sectionId;
        private readonly int _newOrder;

        private const string SQL = @"
            Declare @OldOrder	INT	
	        DECLARE @TermId INT
	        SELECT	@OldOrder = [Order], @TermId = TermId
	        FROM	dbo.Section
	        WHERE SectionId = @SectionId
	
	        IF(@OldOrder<=@NewOrder)
		        Set @NewOrder = @NewOrder + 1
	
	        UPDATE	f
	        SET		f.[Order] = @NewOrder
	        FROM	dbo.Section f 
	        WHERE f.SectionId =  @SectionId
	
	        UPDATE	dbo.Section
	        SET		[Order] = [Order] + 1
	        WHERE	TermId = @TermId AND [Order] >= @NewOrder + 1
	
	        UPDATE	dbo.Section
	        SET		[Order] = [Order] - 1
	        WHERE	TermId = @TermId AND [Order] <= @NewOrder AND SectionId <> @SectionId 
	
	        UPDATE	f
	            SET		f.[Order] = c.ReOrder		
	            FROM	dbo.Section f INNER JOIN 
						            (SELECT SectionId, row_number() over (ORDER BY [Order]) as ReOrder
						             FROM	dbo.Section
						            Where	TermId = @TermId) AS c ON f.SectionId = c.SectionId
	            WHERE	f.TermId = @TermId
	
        ";
        public ChangeSectionOrderDbCommand(int sectionId, int newOrder)
        {
            _sectionId = sectionId;
            _newOrder = newOrder;
        }

        public bool Execute()
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];

            using (var con = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand(SQL, con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SectionId", SqlDbType.Int).Value = _sectionId;
                    cmd.Parameters.Add("@NewOrder", SqlDbType.Int).Value = _newOrder;
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            return true;

        }
    }
}
